import datetime
import pandas as pd
from data_resource.data_bases import engine
from utilities.utilities_statics import factor_standardize
from factorPool.momentums import period_return


def divquality_lastest(enddate):
    """获取红利质量最新选股结果"""
    sql1 = f"""
        select a.code, a.industry_name, a.end_date, a.roe_q, a.roe_yoy_growth as roe_growth,
            b.dv_ratio, c.bp, c.bp_pivot_1y, d.ep, d.ep_pivot_1y
        from quant_research.factor_finance_indicator as a
        left join quant_research.indicator_daily as b
            on a.code=b.code and b.trade_date='2025-10-31'
        left join quant_research.factor_bp_compound as c
            on a.code=c.code and c.trade_date='2025-10-31'
        left join quant_research.factor_ep_compound as d
            on a.code=d.code and d.trade_date='2025-10-31'
        -- 市值大于50亿，股票属于中证1000+中证800成分股, ROE_Q为正
        where a.end_date='{enddate}' and b.circ_mv>=500000 and a.code in (
            select con_code from quant_research.index_constituent where trade_date=(
                select max(trade_date) from quant_research.index_constituent
            ) and index_code in ('000852.SH', '000906.SH')
        ) and a.roe_q >0;
    """

    _raw = pd.read_sql(sql1, engine)

    # 银行股
    _raw_bank = _raw[_raw['industry_name'] == '银行'].copy()
    _raw_bank['dv_ratio'] = factor_standardize(_raw_bank['dv_ratio'], method='zscore', extreme=True)
    _raw_bank['roe_growth'] = factor_standardize(_raw_bank['roe_growth'], method='zscore', extreme=True)
    _raw_bank['roe_q'] = factor_standardize(_raw_bank['roe_q'], method='zscore', extreme=True)
    _raw_bank['bp'] = factor_standardize(_raw_bank['bp'], method='zscore', extreme=True)
    _raw_bank['bp_pivot_1y'] = factor_standardize(_raw_bank['bp_pivot_1y'], method='zscore', extreme=True)

    _raw_bank['signal'] = (_raw_bank['dv_ratio'] + _raw_bank['roe_growth'] + _raw_bank['bp'] +
                           _raw_bank['bp_pivot_1y'] + _raw_bank['roe_q'])
    _raw_bank.sort_values('signal', ascending=False, inplace=True)
    _bank = _raw_bank.iloc[:2, :]

    # 非银行股
    _raw_non_bank = _raw[_raw['industry_name'] != '银行'].copy()
    _raw_non_bank['dv_ratio'] = factor_standardize(_raw_non_bank['dv_ratio'], method='zscore', extreme=True)
    _raw_non_bank['roe_growth'] = factor_standardize(_raw_non_bank['roe_growth'], method='zscore', extreme=True)
    _raw_non_bank['roe_q'] = factor_standardize(_raw_non_bank['roe_q'], method='zscore', extreme=True)
    _raw_non_bank['ep'] = factor_standardize(_raw_non_bank['ep'], method='zscore', extreme=True)
    _raw_non_bank['ep_pivot_1y'] = factor_standardize(_raw_non_bank['ep_pivot_1y'], method='zscore', extreme=True)
    _raw_non_bank['bp'] = factor_standardize(_raw_non_bank['bp'], method='zscore', extreme=True)
    _raw_non_bank['bp_pivot_1y'] = factor_standardize(_raw_non_bank['bp_pivot_1y'], method='zscore', extreme=True)

    _raw_non_bank['signal'] = (_raw_non_bank['dv_ratio'] + _raw_non_bank['roe_growth'] + _raw_non_bank['roe_q'] +
                               _raw_non_bank['ep'] + _raw_non_bank['ep_pivot_1y'] + _raw_non_bank['bp'] +
                               _raw_non_bank['bp_pivot_1y'])
    _raw_non_bank.sort_values('signal', ascending=False, inplace=True)
    _non_bank = _raw_non_bank.iloc[:28, :]

    # 股票池
    strategy_pool = pd.concat([_bank, _non_bank])
    return strategy_pool


def growth_momentum_lastest(enddate):
    """获取成长动量最新选股结果"""
    today = datetime.datetime.today().strftime("%Y-%m-%d")

    sql1 = f"""
        select a.code, a.industry_code, a.signal_growth
        from quant_research.factor_growth as a
        left join quant_research.indicator_daily as b on a.code=b.code and b.trade_date='2025-10-31'
        where a.end_date = '{enddate}' and b.circ_mv>=300000 and a.code in (
            select con_code from quant_research.index_constituent where trade_date=(
                select max(trade_date) from quant_research.index_constituent
                ) and index_code in ('000852.SH', '000906.SH')
            )
        order by signal_growth desc limit 300;
    """
    _raw = pd.read_sql(sql1, engine)
    strategy_pool = _raw['code'].to_list()

    oneyear_before = datetime.datetime.today() - datetime.timedelta(days=365)
    oneyear_before = oneyear_before.strftime("%Y-%m-%d")
    # 计算动量因子
    sql2 = f"""
    -- 申万行业分类重复的取in_date最新的
    with sw_contituent as (
            select l1_name, ts_code
            from (
                select *, row_number() over (PARTITION BY ts_code order by in_date DESC) AS rn
                from quant_research.sw_industry_constituent
            ) as ranked
            where rn=1
        )
        
    select a.ticker as code, a.trade_date as trading, a.close, a.open, b.l1_name as industry_code, c.circ_mv
    from quant_research.market_daily_ts as a
    left join sw_contituent as b on a.ticker=b.ts_code
    left join quant_research.indicator_daily as c on a.ticker=c.code and a.trade_date=c.trade_date

    where a.ticker in (
        select con_code from quant_research.index_constituent where trade_date=(
            select max(trade_date) from quant_research.index_constituent
        ) and index_code in ('000852.SH', '000906.SH')
    ) and a.trade_date > '{oneyear_before}'
    order by a.trade_date;
    """
    raw1 = pd.read_sql(sql2, con=engine)
    raw1['momentum_3M'] = raw1.groupby('code')['close'].transform(lambda x: period_return(
        x, 40, 20))
    raw1['momentum_3M'] = raw1.groupby('trading', as_index=False)['momentum_3M'].transform(
        lambda x: factor_standardize(x))

    _momentum = raw1[raw1['trading'] == pd.to_datetime("2025-10-31").date()]
    _momentum_pool = _momentum[_momentum['code'].isin(strategy_pool)].copy()
    _momentum_pool.sort_values(by='momentum_3M', ascending=False, inplace=True)

    results = _momentum_pool.iloc[:30, :]

    a = results.merge(_raw[['code', 'signal_growth']], on=['code'])
    a['end_date'] = pd.to_datetime("2025-09-30").date()
    return a


if __name__ == '__main__':
    x = growth_momentum_lastest("2025-09-30")
    xx = divquality_lastest("2025-09-30")
    x.to_sql('strategy_growth_momentum', engine, if_exists='append', index=False)
    xx.to_sql('strategy_divquality', engine, if_exists='append', index=False)
